if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PopulateV1SecurityData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PopulateV1SecurityData]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].[PopulateV1SecurityData]
AS
BEGIN
	SET NOCOUNT ON

	-- declare locals
	DECLARE	@orgAccess			VARCHAR(4)
	DECLARE	@portfolioAccess	VARCHAR(4)
	DECLARE	@directAccess		VARCHAR(4)
 	DECLARE @userName			VARCHAR(12)
	
	SET @orgAccess			= 'ORG'
	SET @portfolioAccess	= 'PORT'
	SET @directAccess		= 'DIR'

	-- Cleanup after last run
	truncate table [dbo].tmpV1SecurityData

	DECLARE userNameCursor CURSOR FAST_FORWARD FOR
		select UserName from [dbo].[UserIdsForVerify]

	OPEN userNameCursor
	FETCH NEXT FROM userNameCursor INTO @userName

	WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT @userName
			--- PROTOCOLS!
			-- DIRECT
			insert into [dbo].tmpV1SecurityData
				select a.sFolderNumber, b.sUserName, @directAccess, '', ''
				from tblSecItemChild a, tblRecomAbstract b
				where a.nRECOMAbstractID = b.nRECOMAbstractID
				and b.sUserName = @userName

			-- ORGS (Broken into Dept, Unit & Lab queries since 
			-- that's the way V1 works...
			insert into [dbo].tmpV1SecurityData
			select distinct b.sFolderNumber, c.sUserName, @orgAccess, b.sPIInstitution + ' > ' + b.sPIDept + ' > ' + b.sPIUnit, ''
			from tblRecomInstitutionAddress a
				,tblIRBProtocol b
					,tblRecomAbstract c
			where b.sPIInstitution = a.sPIInstitution
			  and b.sPIDept = a.sPIDept
			  and b.sPIDept <> ''
			  and b.sPIDept <> '(None)'
			  and b.sPIDept <> 'NULL'
			  and b.sPIDept IS NOT NULL
			  and (b.sPIUnit = '(None)' 
					or b.sPIUnit is NULL 
					or b.sPIUnit = '' 
					or b.sPIUnit = 'NULL')
			  AND a.nRoleID is not null
			  and (a.sPIUnit is NULL or a.sPIUnit = '')
			  and a.nRECOMAbstractID = c.nRECOMAbstractID 
				and c.sUserName = @userName

			-- Unit
			insert into [dbo].tmpV1SecurityData
			select distinct b.sFolderNumber, c.sUserName, @orgAccess, b.sPIInstitution + ' > ' + b.sPIDept + ' > ' + b.sPIUnit, ''
				from tblRecomInstitutionAddress a
					,tblIRBProtocol b
						,tblRecomAbstract c
				where b.sPIInstitution = a.sPIInstitution
				  and b.sPIDept = a.sPIDept
				  and b.sPIUnit = a.sPIUnit
				  and b.sPIUnit <> ''
				  and b.sPIUnit <> '(None)'
				  and b.sPIUnit <> 'NULL'
				  and b.sPIUnit IS NOT NULL
				  and (b.sPILab is NULL or b.sPILab = '')
				  AND a.nRoleID is not null
				  and a.nRECOMAbstractID = c.nRECOMAbstractID 
				  and c.sUserName = @userName
				  and (a.sPILab is NULL or a.sPILab = '')
			
			-- LAB	
			insert into [dbo].tmpV1SecurityData
			select distinct b.sFolderNumber, c.sUserName, @orgAccess, b.sPIInstitution + ' > ' + b.sPIDept + ' > ' + b.sPIUnit, ''
				from tblRecomInstitutionAddress a
					,tblIRBProtocol b
						,tblRecomAbstract c
				where b.sPIInstitution = a.sPIInstitution
				  and b.sPIDept = a.sPIDept
				  and b.sPIUnit = a.sPIUnit
				  and b.sPILab = a.sPILab
				  and b.sPILab <> ''
				  and b.sPILab <> '(None)'
				  and b.sPILab <> 'NULL'
				  and b.sPILab IS NOT NULL
				  AND a.nRoleID is not null
				  and a.nRECOMAbstractID = c.nRECOMAbstractID 
				  and c.sUserName = @userName

			-- PI (Direct)
			insert into [dbo].tmpV1SecurityData
			select distinct a.sFolderNumber, c.sUserName, @directAccess, '', ''
				from tblIRBProtocol a, tblRecomAbstract c
				where a.nPIID = c.nRECOMAbstractID
					and c.sUserName = @userName

			-- PORTFOLIO
			insert into [dbo].tmpV1SecurityData
			select distinct a.sFolderNumber, c.sUserName, @portfolioAccess, '', ''
				from tblIRBProtocol a, tblSecPIA b, tblRecomAbstract c
				where a.nPIID = b.nPermissionOwnerID
					and b.nPermissionPIAID = c.nRECOMAbstractID
					and c.sUserName = @userName
			
			-- AGREEMENTS
			-- dept.			
			insert into [dbo].tmpV1SecurityData (FolderNumber, UserName, AccessType, Organization, GrantNumber)
			select distinct NULL, c.sUserName, @orgAccess, 
				b.sResponsibleInstitution + ' > ' + b.sResponsibleDepartment + ' > ' + b.sResponsibleUnit,
				d.sProjectGrantNumber as ProjectGrantNumber
				from tblRecomInstitutionAddress a
					,tblAgreement b
						,tblRecomAbstract c
						,tblProphecyGrantList d
    			where b.sResponsibleInstitution = a.sPIInstitution
				  and b.sResponsibleDepartment = a.sPIDept
				  and b.sResponsibleDepartment <> ''
				  and b.sResponsibleDepartment <> '(None)'
				  and b.sResponsibleDepartment <> 'NULL'
				  and b.sResponsibleDepartment IS NOT NULL
				  and (b.sResponsibleUnit = '(None)' 
						or b.sResponsibleUnit is NULL 
						or b.sResponsibleUnit = '' 
						or b.sResponsibleUnit = 'NULL') 
				  and b.bCurrentStatus = 1
				  AND a.nRoleID is not null
				  and (a.sPIUnit is NULL or a.sPIUnit = '')
				  and a.nRECOMAbstractID = c.nRECOMAbstractID 
				  and c.sUserName = @userName
				  and b.sFolderNumber = d.sFolderNumber 

			-- unit
			insert into [dbo].tmpV1SecurityData (FolderNumber, UserName, AccessType, Organization, GrantNumber)
			select distinct NULL, c.sUserName, @orgAccess, 
				b.sResponsibleInstitution + ' > ' + b.sResponsibleDepartment + ' > ' + b.sResponsibleUnit,
				d.sProjectGrantNumber as ProjectGrantNumber
				from tblRecomInstitutionAddress a
					,tblAgreement b
						,tblRecomAbstract c
						,tblProphecyGrantList d
				where b.sResponsibleInstitution = a.sPIInstitution
				  and b.sResponsibleDepartment = a.sPIDept
				  and b.sResponsibleUnit = a.sPIUnit
				  and b.sResponsibleUnit <> ''
				  and b.sResponsibleUnit <> '(None)'
				  and b.sResponsibleUnit <> 'NULL'
				  and b.sResponsibleUnit IS NOT NULL
				  and (b.sResponsibleLab is NULL or b.sResponsibleLab = '')
				  and b.bCurrentStatus = 1
				  AND a.nRoleID is not null
				  and a.nRECOMAbstractID = c.nRECOMAbstractID 
				  and (a.sPILab is NULL or a.sPILab = '')
				  and c.sUserName = @userName
				  and b.sFolderNumber = d.sFolderNumber 

			-- lab (?)
			insert into [dbo].tmpV1SecurityData (FolderNumber, UserName, AccessType, Organization, GrantNumber)
			select distinct NULL, c.sUserName, @orgAccess, 
				b.sResponsibleInstitution + ' > ' + b.sResponsibleDepartment + ' > ' + b.sResponsibleUnit,
				d.sProjectGrantNumber as ProjectGrantNumber
				from tblRecomInstitutionAddress a
					,tblAgreement b
						,tblRecomAbstract c
						,tblProphecyGrantList d
				where b.sResponsibleInstitution = a.sPIInstitution
				  and b.sResponsibleDepartment = a.sPIDept
				  and b.sResponsibleUnit = a.sPIUnit
				  and b.sResponsibleLab = a.sPILab
				  and b.sResponsibleLab <> ''
				  and b.sResponsibleLab <> '(None)'
				  and b.sResponsibleLab <> 'NULL'
				  and b.sResponsibleLab IS NOT NULL
				  and b.bCurrentStatus = 1
				  AND a.nRoleID is not null
				  and a.nRECOMAbstractID = c.nRECOMAbstractID 
				  and c.sUserName = @userName
				  and b.sFolderNumber = d.sFolderNumber

			-- PI
			insert into [dbo].tmpV1SecurityData
			select distinct NULL, c.sUserName, @directAccess, '', d.sProjectGrantNumber as ProjectGrantNumber
			from tblAgreement a, tblRecomAbstract c, tblProphecyGrantList d
			where a.nPIID = c.nRECOMAbstractID
			  and a.bCurrentStatus = 1
				and c.sUserName = @userName
				and a.sFolderNumber = d.sFolderNumber

			--PIA
			insert into [dbo].tmpV1SecurityData
			select distinct NULL, c.sUserName, @portfolioAccess, '', d.sProjectGrantNumber as ProjectGrantNumber
				from  tblAgreement a, tblSecPIA b, tblRecomAbstract c, tblProphecyGrantList d
			where a.nPIID = b.nPermissionOwnerID
			  and b.nPermissionPIAID = c.nRECOMAbstractID
			  and a.bCurrentStatus = 1  
					and c.sUserName = @userName
					and a.sFolderNumber = d.sFolderNumber


			FETCH NEXT FROM userNameCursor INTO @userName

		END

	CLOSE userNameCursor
	DEALLOCATE userNameCursor


END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

